import pymysql

# 连接参数
mysql_conf = {
    "host": "localhost",
    "user": "root",
    "port": 3306,
    "password": "xxxxxxxx",
    "db": "yxt_knowlage"
}

# 建立连接
non_connnect = pymysql.connect(**mysql_conf)
# 拿到光标,实际操作都需要光标来进行
cursor_local = non_connnect.cursor()


class Talent_class_input():
    def create_table(self):
        sql = "USE yxt_knowlage;"
        cursor_local.execute(sql)
        SS = "CREATE TABLE talent_class(id INT PRIMARY KEY auto_increment, NAME VARCHAR(225), study_time INT, CREATE_TIME TIMESTAMP);"
        cursor_local.execute(SS)
        non_connnect.commit()

    def insert_one(self):
        # 设定执行的内容
        sql = f"insert into talent_class(name) values ('ggsmd')"
        # 执行内容
        cursor_local.execute(sql)
        # 确保步骤完成
        non_connnect.commit()

    def insert_many1(self):
        sql = "insert into talent_class (name) values (%s)"
        cursor_local.executemany(sql, [(f"{i}",) for i in range(1, 20, 2)])
        non_connnect.commit()

    def insert_many(self):
        sql = "insert into talent_class (name,sex) values (%s, %s)"
        cursor_local.executemany(sql, [(f"{i}", f"{i}") for i in range(10)])
        non_connnect.commit()

    def get_name(self):
        sql = "select *from talent_class"
        # 执行语句
        cursor_local.execute(sql)
        # 获取返回值，返回值是个tuple数列（元组里是元组）
        array_name = cursor_local.fetchall()
        # 获取元组
        for name in array_name:
            # 获取其中的值
            print(name[1])


class Talent_class_other:
    def update_data(self):
        sql = "update talent_class set %s=%s where %s=%s" % (
        input("输入要改的字段名"), input("输入改后的值"), input("输入改之前的字段名"), input("输入改之前的字段值"))
        cursor_local.execute(sql)
        non_connnect.commit()

    def delete_data(self):
        sql = "delete from talent_class where %s=%s" % (input("输入要删除的字段名"), input("输入要删除的值"))
        cursor_local.execute(sql)
        non_connnect.commit()

    def retrieve_data(self):
        sql = "select %s from talent_class where %s=%s" % (input("输入要展示的字段名"), input("输入要检索的字段名"), input("输入检索的值"))
        cursor_local.execute(sql)
        non_connnect.commit()

    def show_data(self):
        sql = "select * from talent_class"
        cursor_local.execute(sql)
        array_data = cursor_local.fetchall()
        for item in array_data:
            print(item)


class_1 = Talent_class_input()
class_2 = Talent_class_other()